
--nvarchar go tieng viet
if exists (select * from dbo.SysObjects where name='dean')
begin
	drop table dean
end
Go
create table dean
(
TENDA		nvarchar(20),
MADA		int,
DDIEM_DA	nvarchar(30),
PHONG		int
primary key (MADA)
)
insert into dean(TENDA,MADA,DDIEM_DA,PHONG) values('sanpham x',2,'nhatrang',1)
insert into dean(TENDA,MADA,DDIEM_DA,PHONG) values('sanphamz',3,'TPHCM',5)
insert into dean(TENDA,MADA,DDIEM_DA,PHONG) values('sanphamy',5,'vungtau',1)
insert into dean(TENDA,MADA,DDIEM_DA,PHONG) values('tinhochoa',10,'Hanoi',4)
insert into dean(TENDA,MADA,DDIEM_DA,PHONG) values('hoahoc',40,'BienHoa',5)

--tao table diadiem_phg
drop table DIADIEM_PHG
if exists (select * from dbo.sysobjects where name='DIADIEM_PHG')
begin
	drop table DIADIEM_PHG
end
Go
create table DIADIEM_PHG
(
MAPHG   int,
DIADIEM nvarchar(30),
primary key(MAPHG,DIADIEM)
)
--insert du lieu
insert into DIADIEM_PHG(MAPHG,DIADIEM) values (1,'vungtau')
insert into DIADIEM_PHG(MAPHG,DIADIEM) values (2,'TPHCM')
insert into DIADIEM_PHG(MAPHG,DIADIEM) values (3,'NhaTrang')
insert into DIADIEM_PHG(MAPHG,DIADIEM) values (4,'TPHCM')
insert into DIADIEM_PHG(MAPHG,DIADIEM) values (5,'HaNoi')
insert into DIADIEM_PHG(MAPHG,DIADIEM) values (6,'TPHCM')

--tao table nhanvien
if exists (select *from dbo.sysobjects where name = 'NHANVIEN')
begin
	drop table NHANVIEN
end
Go
create table NHANVIEN
(
HONV	nvarchar(10),
TENLOT	nvarchar(10),
TENNV	nvarchar(10),
MANV	varchar(9),
NGSINH	datetime,
DCHI	nvarchar(30),
PHAI	varchar(10),
LUONG	numeric,
MA_NQL	varchar(10),
PHG		int
primary key (MANV)
)
insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG) 
values ('Cao','si','ki','123123456',09/08/1986,'123 le loi Q1 TPHCM','nam',15000,'159753123',5)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG) 
values ('Dinh','Ba','Tien','123456789',09/01/1955,'731 Tran Hung Dao Q1 TPHCM','nam',30000,'159933123',5)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG) 
values ('Truong','Le','Doan','147852369',15/05/1986,'22/41/2 Le Van Tho F11 Go Vap','nu',16500,'147852369',1)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG) 
values ('Nguyen','Thanh','Tung','333445555',08/12/1945,'638 Nguyen Van Cu Q5 TpHCM','nam',40000,'123456789',5)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG) 
values ('Le','Huynh','Duc','666884444',01/06/1986,'126 Nguyen Van Cu Q5 Tp Hcm','Nam',27000,'666884444',1)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
 values ('Vuong','Ngoc','Quyen','888665555',15/09/1965,'414 NO TRANG LONG Q BINH THANH TPHCM','Nam',29400,'999887777',4)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG)
 values ('Le','Thi','Nhan','987654321',20/06/1931,'291 Ho Van Hue Qphu Nhuan TpHCM','Nu',43000,'123456789',5)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG) 
values ('Vo','Duc','Minh','987987987',16/09/1985,'414 NO TRANG LONG Q BINH THANH TPHCM','Nam',30000,'123456789',5)

insert into NHANVIEN(HONV,TENLOT,TENNV,MANV,NGSINH,DCHI,PHAI,LUONG,MA_NQL,PHG) 
values ('Bui','Thuy','Vu','999887777',19/07/1958,'332 Nguyen Thai Hoc Q1 TpHCM','Nam',25000,'123456789',4)

--tao phong ban
if exists (select *from dbo.sysobjects where name='PHONGBAN')
begin
	drop table PHONGBAN
end
Go
create table PHONGBAN
(
TENPHG	nvarchar(10),
MAPHG	int,
TRPHG	numeric,
NG_NHANCHUC	datetime,
primary key (MAPHG)
)
insert into PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC) values ('quan ly',1,666884444,19/06/1971)
insert into PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC) values ('To chuc',2,987987987,15/01/2001)
insert into PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC) values ('Dieu Hanh',4,123456789,01/01/1985)
insert into PHONGBAN(TENPHG,MAPHG,TRPHG,NG_NHANCHUC) values ('Ngien Cuu',5,333445555,22/05/1978)

--tao table phan cong
	if exists (select * from dbo.sysobjects where name ='PHANCONG')
begin
	drop table PHANCONG
end
Go
create table PHANCONG
(
MA_NVIEN	varchar(10),
SODA		int,
THOIGIAN	varchar(10),
primary key (MA_NVIEN)
)
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('123123456',2,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('123123456',40,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('123456789',40,'10.00')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('147852369',40,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('333445555',2,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('333445555',40,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('666884444',40,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('888665555',2,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('987654321',40,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('987987987',40,'')
	insert into PHANCONG(MA_NVIEN,SODA,THOIGIAN) values ('999887777',40,'')

--tao table THANNHAN
if exists (select *from dbo.sysobjects where name ='THANNHAN')
begin
	drop table THANNHAN
end
Go
create table THANNHAN
(
MA_NVIEN	varchar(10),
TENTN		varchar(10),
PHAI		varchar(10),
NGSINH		datetime,
QUANHE		varchar(10),
primary key (MA_NVIEN,TENTN)
)
insert into THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE) values ('333445555','Chau Long','nam',02/01/2005,'chong')
insert into THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE) values ('333445555','Khang','nam',25/10/1973,'con trai')
insert into THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE) values ('333445555','Ngoc Phi','nu',05/04/1976,'con gai')
insert into THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE) values ('987654321','Dang','nam',29/02/1932,'vo chong')
insert into THANNHAN(MA_NVIEN,TENTN,PHAI,NGSINH,QUANHE) values ('999887777','Toan','nam',02/01/1986,'chong')



